<?php 
class boutique_models_creer extends CI_Model{
	public function __construct(){
		$this->load->database();
	}
   public function create($product){
   		$product_photo=$product_options=array();
   		
   		if(isset($product->photo)){
	   		$product_photo=$product->photo;
	   		unset($product->photo);
   		}
   		if(isset($product->option)){
	   		$product_options=$product->option;
		   	unset($product->option);
   		}
	   	if(isset($product->prd_id))unset($product->prd_id);
	   	
   		$product->prd_link=self::valide_link($product->prd_link);
   		//creer produit
	   	$this->db->insert('product', $product);
	   	//prendre id auto increment
	   	$product->prd_id=$this->db->insert_id();
	   	
	   	//ajout option pour les filtre	   	
	   	if(isset($product_options) and !empty($product_options)){
		   		foreach ($product_options as $option_id=>$valeur_option){
		   			$product_option[]=array(
		   					'prd_id'=>$product->prd_id,
		   					'option_id'=>$option_id,
		   					'valeur_option'=>$valeur_option,
		   					'numeric_option'=>is_numeric($valeur_option)?$valeur_option:null
		   			);
		   		}
		   		$this->db->insert_batch('product_option', $product_option);
	   	}
		//ajout photo
	   	if(!empty($product_photo)){
	   		foreach ($product_photo as $photoName){
	   			$photo[]=array(
	   					'prd_id'=>$product->prd_id,
	   					'link_photo'=>$photoName);
	   		}
	   		$this->db->insert_batch('product_photo', $photo);
	   		
	   	}
	   	
   		return 	$product->prd_id;
   }
    public function get_month_year_create($limit=''){  
    	if($limit) $limit=" LIMIT $limit";  	
		$get= $this->db->query("SELECT DISTINCT DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%Y%m' )AS YearMonth,
		    	DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%m' )AS month,
		    	DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%Y' )AS year
				from product  $limit");
		return $get->result();
    
    }
    public function get_info_product($prd_id){
    	if(empty($prd_id)) return false;
    	$query = $this->db->get_where('product', array('prd_id' => $prd_id), 1);
    	$product=new stdclass;
    	if($query->num_rows()){
    		$row= $query->result();
    		$product=$row[0];
    		$category=$this->load->module('category/admin');
    		$product->link_photo='no-photo.gif';
    		$product->link_cat_product=$category->get_name_category($product->category_id);
    		//si il y a des options
    		$query = $this->db->get_where('product_option', array('prd_id' => $prd_id));
    		if($query->num_rows()){
    			foreach ($query->result() as $row){
    				$product->option[$row->option_id]=$row->valeur_option;
    			}
    		}
    		//si il y a des photos
    		$query = $this->db->get_where('product_photo', array('prd_id' => $prd_id));
    		if($query->num_rows()){
    			$i=0;
    			foreach ($query->result() as $row){
    				$product->photo[$row->id]=$row->link_photo;
    				if(!$i++)$product->link_photo=$row->link_photo;
    			}
    		}
    		$query = $this->db->get_where('departement_ville', array('Cp' => $product->prd_codepostal), 1);
    		$row= $query->result();
    		$product->ville=$row[0]->N_Com_min;
    		return $product;
    	}
    	return false;
    }
    // si option est valeur à saissir Min Max on change un table
    /*
     * exeple: un voiture annee min 1990 annee max 2000
     * option[1]=1990 option[2]=2000
     * parent de option 1 et 2 est 80 
     * donc option[80]= array(1990,2000)
     * */
    private function generateOptionMinMax($options) {
    		foreach ($options as $id_type=>$option){
    			$parent =self::get_parentOption($id_type);
    			if($parent){
    				$options[$parent][]=$option;
    				unset($options[$id_type]);
    			}
    			
    		}
    		//sort min max
    		foreach ($options as $id_type=>$option){
    			if(is_array($option)){
    				sort($option);
    				if(isset($option[0]) and empty($option[0])) unset($option[0]);
    				if(isset($option[1]) and empty($option[1])) unset($option[1]);
    				if(!empty($option)) $options [$id_type]=$option;
    			}
    			if(empty($option)) unset($options [$id_type]);
    		}
    		return $options;
    } 
    /* generate SQL pour tous les options
     * 
     * */
    private function generateOptionSQL($options) {
    		foreach ($options as $id_type=>$option){
    			$champOptionValue=is_numeric($option)?'numeric_option':'valeur_option';
    			if(is_array($option)){
    				if( isset($option[0]) and $option[0]  ) {
    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue > '{$option[0]}')";
    				}
    				if( isset($option[1]) and $option[1]  ) {
    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue < '{$option[1]}')";
    				}
    				if( isset($option[0]) and $option[0] and
    					isset($option[1]) and $option[1] ) {
    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue BETWEEN '{$option[0]}' and '{$option[1]}')";
    				}
    					
    			}else{
    				$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue='".addslashes($option)."')";
    			}
    			$alias_table[]=$sql;
    		}
    		$from='';
    		foreach($alias_table as $index=>$sqlT){
    			$from.=$sqlT." T$index ";
    			
    			if($index) $from.= " \n ON T0.prd_id= T$index.prd_id ";
    			
    			if($index!=count($alias_table)-1) $from.=" \n INNER JOIN ";
    			
    		}
    		$select= "SELECT T0.prd_id  FROM $from";
    		return $select;
    } 
    /*
     * recuperer id parent apres on remplacer id actuelle par id parent
     * retourner false si 0
     * */
    private function get_parentOption($id_type) {
    	$query = $this->db->get_where('options', array('id_type' => $id_type), 1);
    	if($query->num_rows() ){
    		$row=$query->result();
    		return $row[0]->parent;
    	}
    	return false;
    }
    
    /*
     * créer une table temporaire pour stocker tous prd_id
     * retourner false si 0
     * */
    private function set_tableTemp($nameTempTable) {
    	$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS `$nameTempTable`
		   (`id` INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
		   `prd_id` INT(10) NOT NULL) ENGINE = MyISAM";
		$this->db->query($sql);  
    }

    /*
     * supprime une table temporaire 
    * retourner false si 0
    * */
    /*
    private function drop_tableTemp($nameTempTable) {
    	$sql = "DROP TEMPORARY TABLE IF  EXISTS `$nameTempTable`";
    	$this->db->query($sql);
    }

    public function get_list_product($filter=array(),$options=array(),$sort=array(),$paging=array()){
    	$limit=$paging['number_affiche'];
    	$current=$paging['page_current']*$limit;
    	$join = '';
    	$where='';
    	$orderBy='';
    	$condition=array();
    	if(!empty($sort))
	    	foreach ($sort as $sortName=>$valueSort)
	    		$orderBy="ORDER BY $sortName $valueSort";
    	if(!empty($options)){
    		$options=self::generateOptionMinMax($options);
    		$optionSQL=self::generateOptionSQL($options);
    		
    		//creer table temp pour stocker les id
    		$tableTemp= substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyz"), 0, 10);
    		self::set_tableTemp($tableTemp);
    		//remplir temp table    		
    		$this->db->query("INSERT INTO $tableTemp (prd_id) $optionSQL");
    		$join = " INNER JOIN $tableTemp ON $tableTemp.prd_id= product.prd_id ";
    	}
    	if(!empty($filter)){
    		if($filter['category']){
    			$condition[]='category_id='.$filter['category'];
    		}
    		if($filter['date']){
    			$condition[]="DATE_FORMAT(DATE( FROM_UNIXTIME(product.date_validate ) ),'%Y%m' )='{$filter['date']}'";
    		}
    		if(!empty($condition))$where=' WHERE '.implode(' AND ',$condition);
    	}
    	$sql="SELECT DISTINCT product.prd_id 
				FROM product 
				$join 
		    	$where 
		    	$orderBy
		    	LIMIT $current,$limit
    		";
		$get= $this->db->query($sql);
		//enlever temp table apres avoir les resultat
		if(isset($tableTemp))self::drop_tableTemp($tableTemp) ;
		
		return $get->result();
    
    }*/
    public function get_total_product($active=FALSE){
    	if($active) $active=" WHERE prd_active=1";
    	else $active='';
    	$get= $this->db->query("SELECT count(*) as numProduct FROM product $active");
    	$row=$get->result();
    	
    	return $row[0]->numProduct;
    }
    public function deactive($id){
    	$this->db->update('product', array('prd_active'=>0), "prd_id = $id");
    }
    public function reactive($id){
    	$this->db->update('product', array('prd_active'=>1), "prd_id = $id");
    }
    public function delPhoto($idPhoto){
    	$arrayId=array();
    	if(!is_array($idPhoto))$arrayId[]=$idPhoto;
    	else $arrayId=$idPhoto;
    	foreach($arrayId as $id){
    		$this->db->delete('product_photo', array('id' => $id));
    	}
    }
    public function edit($product){
    	//ajout option pour les filtre
    	if(isset($product->option) and !empty($product->option)){
    		foreach ($product->option as $option_id=>$valeur_option){
    			$product_options[]=array(
    					'prd_id'=>$product->prd_id,
    					'option_id'=>$option_id,
    					'valeur_option'=>$valeur_option,
    					'numeric_option'=>is_numeric($valeur_option)?$valeur_option:null
    			);
    		}
    		if(isset($product_options)){
    			foreach ($product_options as $product_option){
    				$query = $this->db->get_where('product_option', 
    										array(
	    										'prd_id' => $product_option['prd_id'],
	    										'option_id' => $product_option['option_id']
    										)
    					);
    				if($query->num_rows()){
    					$this->db->update('product_option',$product_option,
    										array(
	    										'prd_id' => $product_option['prd_id'],
	    										'option_id' => $product_option['option_id']
    										)
    					);
    				}else{
    					$product_option_add[]=$product_option;
    				}
    			}
    			if(isset($product_option_add)) 
    				$this->db->insert_batch('product_option', $product_option_add);
    		}
    		unset($product->option);
    	}
    	//ajout photo
    	if(isset($product->photo)){
    		if(!empty($product->photo)){
    			
	    		foreach ($product->photo as $photoName){
	    			$product_photo[]=array(
	    					'prd_id'=>$product->prd_id,
	    					'link_photo'=>$photoName);
	    		}
	    		$this->db->insert_batch('product_photo', $product_photo);
    		}
    		unset($product->photo);
    	}
    	$product->prd_link=self::valide_link($product->prd_link);
    	$this->db->update('product', $product, "prd_id = {$product->prd_id}");

    }
    private function valide_link($page_link){
    	$query = $this->db->get_where('product', array('prd_link' => $page_link), 1);
    	if($query->num_rows()){
    		$random=substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyz"), 0, 4);
    		return $page_link.'-'.$random;
    	}
    	return $page_link;
    }

    public function check_doublon($shop){
    
    	$query = $this->db->get_where('shop',
    			array('email' => $shop->email,
    					'company_name' => $shop->company_name,
    					'siren' => $shop->siren), 1);
    	if($query->num_rows()) return false;
    	return true;
    }
    public function get_doublon($shop){
    
    	$query = $this->db->get_where('shop',
    			array('email' => $shop->email,
    					'company_name' => $shop->company_name,
    					'siren' => $shop->siren), 1);
    	if($query->num_rows()) {
    		$row=$query->result();
    		return $row[0]->id;
    	}
    	return true;
    }
}